package autoTest.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import autoTest.device.Platform;

public class DataManager {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String username = "root";
    private static String password = "";
    private static String DB_URL = "jdbc:mysql://localhost:3306/";
    private static String databaseName = "serverdb";
    private static String url = "jdbc:mysql://localhost:3306/" + databaseName;
    
	public synchronized static void createDatabase() {
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(DB_URL, username, password);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			
			String sql = "create database if not exists " + databaseName;
			int ret = stmt.executeUpdate(sql);
			conn.commit();
			System.out.println("Database created successfully...  ret=" + ret);
		} catch (SQLException se) {
			se.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(stmt);
			closeConnection(conn);
		}
		System.out.println("Goodbye!");
		createTable();
	}
	
	private static void createTable() {
		Connection conn = getConn();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			
			String sql = "create table if not exists allResult " +
		    		"(id int not null auto_increment, " +
		    		" task Text, " + 
		    		" result Text, " + 
		    		" devices Text, " + 
		    		" PRIMARY key (id))";
		    stmt.executeUpdate(sql);
		    sql = "create table if not exists blackList " +
		    		"(id int not null auto_increment, " +
		    		" platform Text, " + 
		    		" type int not null, " + 
		    		" blackListSet Text, " +
		    		" PRIMARY key (id))";
		    stmt.executeUpdate(sql);
		    
		    conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closePreparedStatement(stmt);
			closeConnection(conn);
		}
	}
	
	private static Connection getConn() {
	    Connection conn = null;
	    try {
	        Class.forName(driver); //classLoader,加载对应驱动
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	        conn.setAutoCommit(false);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
	
	private static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void closePreparedStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void closePreparedStatement(PreparedStatement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static int insertBlackList(Platform platform, int type, String allCase) {
		int i = 0;
		Connection conn = getConn();
		String sql = "insert into blackList (platform,type,blackListSet) values(?,?,?)";
		
		PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        System.out.println("[insertBlackList] platform:" + platform.valueStr() + ",type:" + type + ",caseStr:" + allCase);
	        pstmt.setString(1, platform.valueStr());
	        pstmt.setInt(2, type);
	        pstmt.setString(3, allCase);
	        i = pstmt.executeUpdate();
	        conn.commit();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	
	public static int putBlackList(Platform platform, int type, Set<String> allCase) {
		String caseStr = new String("");
		for (String ele : allCase) {
			caseStr = caseStr + ele + ",";
		}
		if (caseStr.length() >= 1) {
			caseStr = caseStr.substring(0, caseStr.length() - 1);
		}
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "update blackList set blackListSet='" + caseStr + "' where platform='" + platform.valueStr() + "' and type=" + type;
	    System.out.println("[updateBlackList] sql " + sql);
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        conn.commit();
	        System.out.println("[updateBlackList] resut: " + i);
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    if (i <= 0) {
	    	i = insertBlackList(platform, type, caseStr);
	    }
	    
    	closePreparedStatement(pstmt);
		closeConnection(conn);
		System.out.println("[put] put row:" + i);
	    return i;
	}
	
	public static Set<String> getBlackList(Platform platform, int type) {
		String allCaseStr = new String("");
	    Connection conn = getConn();
	    String sql = "select * from blackList where platform like '" + platform.valueStr() + "' and type=" + type;
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        System.out.println(sql);
	        int col = rs.getMetaData().getColumnCount();
	        System.out.println("============================");
	        int row = 0;
	        while (rs.next()) {
	        	row++;
	        	allCaseStr = allCaseStr + rs.getString(4);
	            System.out.println("allCaseStr -> " + allCaseStr);
	        }
	        
	        System.out.println("row -> " + row);;
	        if (row <= 0) {
	        	return null;
	        } else if (row >= 2) {
	        	return null;
	        }
	        System.out.println("============================");
	        conn.commit();
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return null;
	    } finally {
	    	closePreparedStatement(pstmt);
			closeConnection(conn);
	    }
	    
	    String[] tmp = allCaseStr.split(",");
	    Set<String> ret = new HashSet<String>();
	    for (String ele : tmp) {
	    	ret.add(ele);
	    }
	    return ret;
	}
	
	public synchronized static int removeBlackListByInfo(Platform platform, int type) {
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "delete from blackList where platform='" + platform.valueStr() + "' and type=" + type;
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("resutl: " + i);
	        conn.commit();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	    	closePreparedStatement(pstmt);
			closeConnection(conn);
	    }
	    return i;
	}
	
	private static int insertResult(Connection conn, DataElement ele) {
	    int i = 0;
	    String sql = "insert into allResult (task,result,devices) values(?,?,?)";
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        System.out.println("task:" + ele.getTaskId() + ",result:" + ele.getResultStr() + ",devices:" + ele.getDeviceSet());
	        pstmt.setString(1, ele.getTaskId());
	        pstmt.setString(2, ele.getResultStr());
	        pstmt.setString(3, ele.getDeviceSet());
	        i = pstmt.executeUpdate();
	        conn.commit();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	
	public synchronized static int putResult(DataElement ele) {
//		getAll();
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "update allResult set result='" + ele.getResultStr() + "', devices='" + ele.getDeviceSet() +"' where task='" + ele.getTaskId() + "'";
	    System.out.println("[put] sql " + sql);
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        conn.commit();
	        System.out.println("[put] resut: " + i);
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    if (i <= 0) {
	    	i = insertResult(conn, ele);
	    }
	    
    	closePreparedStatement(pstmt);
		closeConnection(conn);
		System.out.println("[put] put row:" + i);
//		getAll();
	    return i;
	}
	
	public synchronized static DataElement getResultElement(String taskId) {
		DataElement ret = new DataElement();
	    Connection conn = getConn();
	    String sql = "select * from allResult where task like '" + taskId + "'";
	    PreparedStatement pstmt = null;
//	    Statement statement = null;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        System.out.println(sql);
//	        statement = conn.createStatement();
	        int col = rs.getMetaData().getColumnCount();
	        System.out.println("============================");
	        int row = 0;
	        while (rs.next()) {
	        	row++;
	            for (int i = 1; i <= col; i++) {
	            	if (i == 2) {
	            		ret.setTaskId(rs.getString(2));
	            	} else if (i == 3) {
	            		ret.setResult(rs.getString(3));
	            	} else if (i == 4) {
	            		ret.setDeviceSet(rs.getString(4));
	            	}
	            }
	            System.out.println("task -> "+ ret.getTaskId() + ", result -> " + ret.getResultStr() + ", devices -> " + ret.getDeviceSet());
	        }
	        
	        System.out.println("taskId " + taskId + ", row -> " + row);;
	        if (row <= 0) {
	        	return null;
	        } else if (row >= 2) {
	        	getAllResultByTaskId(taskId);
	        	return null;
	        }
	        System.out.println("============================");
	        conn.commit();
	    } catch (SQLException e) {
	        e.printStackTrace();
	        return null;
	    } finally {
	    	closePreparedStatement(pstmt);
			closeConnection(conn);
	    }
	    return ret;
	}
	
//	private static Integer getAll() {
//	    Connection conn = getConn();
//	    String sql = "select * from allResult";
//	    PreparedStatement pstmt;
//	    try {
//	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
//	        ResultSet rs = pstmt.executeQuery();
//	        int col = rs.getMetaData().getColumnCount();
//	        System.out.println("--------------------------->");
//	        while (rs.next()) {
//	            for (int i = 1; i <= col; i++) {
//	                System.out.print(rs.getString(i) + "\t");
//	                if ((i == 2) && (rs.getString(i).length() < 60)) {
//	                    System.out.print("\t");
//	                }
//	            }
//	            System.out.println("");
//	        }
//	        System.out.println("<---------------------------");
//	    } catch (SQLException e) {
//	        e.printStackTrace();
//	    }
//	    return null;
//	}
	
	private static Integer getAllResultByTaskId(String taskId) {
	    Connection conn = getConn();
	    String sql = "select * from allResult where task='" + taskId + "'";
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement)conn.prepareStatement(sql);
	        ResultSet rs = pstmt.executeQuery();
	        conn.commit();
	        return rs.getRow();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	    	closePreparedStatement(pstmt);
			closeConnection(conn);
	    }
	    return null;
	}
	
	public synchronized static int removeResult(String taskId) {
	    Connection conn = getConn();
	    int i = 0;
	    String sql = "delete from allResult where task='" + taskId + "'";
	    PreparedStatement pstmt = null;
	    try {
	        pstmt = (PreparedStatement) conn.prepareStatement(sql);
	        i = pstmt.executeUpdate();
	        System.out.println("resutl: " + i);
	        conn.commit();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	    	closePreparedStatement(pstmt);
			closeConnection(conn);
	    }
	    return i;
	}
}
